package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPaymentBackHistoryDao;
import com.boss.salary.entity.SalaryBankPayBack;
import com.boss.salary.entity.SalaryPaymentRequisitionBack;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class PaymentBackHistoryDaoImpl  extends BaseDaoImpl implements IPaymentBackHistoryDao {
    @Override
    public void batchInsertPayBackHistoryData(List<SalaryPaymentRequisitionBack> salaryPaymentRequisitionBacks, Map<String, String> monthMap) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_PAYMENT_HIS);
        sql.append("(id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, pay_app_no, ")
                .append(" bgt_id, pay_app_amt, fund_type, payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, use_name, exp_func, is_deleted,mof_div_code,salary_month )");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramList = new ArrayList<>(salaryPaymentRequisitionBacks.size());
        for(SalaryPaymentRequisitionBack salaryPaymentRequisitionBack : salaryPaymentRequisitionBacks){
            Object[] objects = new Object[]{
                    SysUtils.generateGuid(), salaryPaymentRequisitionBack.getBizKey(), salaryPaymentRequisitionBack.getCreateTime(),
                    salaryPaymentRequisitionBack.getUpdateTime(), salaryPaymentRequisitionBack.getAgencyId(),
                    salaryPaymentRequisitionBack.getAgencyName(), salaryPaymentRequisitionBack.getPayId(), salaryPaymentRequisitionBack.getPayAppNo(),
                    salaryPaymentRequisitionBack.getBgtId(),
                    salaryPaymentRequisitionBack.getPayAppAmt(), salaryPaymentRequisitionBack.getFundType(), salaryPaymentRequisitionBack.getPayeeAcctName(),
                    salaryPaymentRequisitionBack.getPayeeAcctNo(), salaryPaymentRequisitionBack.getPayeeAcctBankName(), salaryPaymentRequisitionBack.getPayType(),
                    salaryPaymentRequisitionBack.getProCat(), salaryPaymentRequisitionBack.getGovBgtEco(), salaryPaymentRequisitionBack.getDepBgtEco(),
                    salaryPaymentRequisitionBack.getPro(), salaryPaymentRequisitionBack.getUseName(), salaryPaymentRequisitionBack.getExpFunc(),
                    salaryPaymentRequisitionBack.getIsDeleted(),salaryPaymentRequisitionBack.getMofDivCode(),monthMap.get(salaryPaymentRequisitionBack.getAgencyId())
            };
            paramList.add(objects);
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 24; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deletePayBackData(String agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete  from ").append(TableName.ALARY_PAYMENT_BACK);
        sql.append(" where  agency_id=? ");
//        sql.append(" where  ").append(SysUtils.inSql(" agency_id ",agencyIds));
        this.getjdbcTemplate().update(sql.toString(),agencyIds);
    }

    @Override
    public  List<SalaryPaymentRequisitionBack> getPayBackData(String agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ")
                .append(" id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, pay_app_no, ")
                .append(" bgt_id, pay_app_amt, fund_type, payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, use_name, exp_func, is_deleted, mof_div_code from ")
                .append(TableName.ALARY_PAYMENT_BACK);

        sql.append(" WHERE is_deleted = ? and  agency_id = ? ");
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE , agencyIds
        };
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryPaymentRequisitionBack.class), objects);
    }

    @Override
    public void batchInsertBankBackHistoryData(List<SalaryBankPayBack> bankBacks, Map<String, String> monthMap) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_BANK_HIS);
        sql.append("(id, biz_key, create_time, update_time, iden_no, person_name, old_sala_card_no, new_sala_card_no,  ")
                .append("bank_back_code, bank_back_remark, operate_code, is_update, verified_in_agency_code, verified_code, is_recreate, is_long_use, is_send, ")
                .append(" mof_div_code, month_id, new_person_name, new_iden_no, new_salary_bank, person_id, salary_month, agency_id )");
        sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        List<Object[]> paramList = new ArrayList<>(bankBacks.size());
        for(SalaryBankPayBack bankBack : bankBacks){
            Object[] objects = new Object[]{
                    SysUtils.generateGuid(), bankBack.getBizKey(),bankBack.getCreateTime(),
                    bankBack.getUpdateTime(), bankBack.getIdenNo(),
                    bankBack.getPersonName(), bankBack.getOldSalaCardNo(), bankBack.getNewSalaCardNo(),
                    bankBack.getBankBackCode(),
                    bankBack.getBankBackRemark(), bankBack.getOperateCode(), bankBack.getIsUpdate(),
                    bankBack.getVerifiedInAgencyCode(), bankBack.getVerifiedCode(), bankBack.getIsRecreate(),bankBack.getIsLongUse(),bankBack.getIsSend(),
                    bankBack.getMofDivCode(), bankBack.getMonthId(), bankBack.getNewPersonName(),
                    bankBack.getNewIdenNo(), bankBack.getNewSalaryBank(), bankBack.getPersonId(),
                    monthMap.get(bankBack.getAgencyId()),bankBack.getAgencyId()
            };
            paramList.add(objects);
        }

//        super.getJdbcTemplate().update(sql.toString(), objects);
        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 25; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deleteBankBackData(String agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete  from ").append(TableName.SALARY_BANK_BACK);
        sql.append(" where  agency_id=? ");
//        sql.append(" where  ").append(SysUtils.inSql(" agency_id ",agencyIds));

        this.getjdbcTemplate().update(sql.toString(),agencyIds);
    }

    @Override
    public List<SalaryBankPayBack> getBankBackData(String agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ")
                .append("id, biz_key, create_time, update_time, iden_no, person_name, old_sala_card_no, new_sala_card_no,  ")
                .append("bank_back_code, bank_back_remark, operate_code, is_update, verified_in_agency_code, verified_code, is_recreate, is_long_use, is_send, is_update_person, ")
                .append(" mof_div_code, month_id, new_person_name, new_iden_no, new_salary_bank, person_id, agency_id from ")
                .append(TableName.SALARY_BANK_BACK);
        sql.append(" WHERE  agency_id=? ");
//                .append(SysUtils.inSql(" agency_id ",agencyIds));

//            return this.getjdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(SalaryBankPayBack.class));
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBankPayBack.class),agencyIds);
    }

    @Override
    public List<SalaryPaymentRequisitionBack> getPaymentHisDatas(String month, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ")
                .append("id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, pay_app_no, ")
                .append(" bgt_id, pay_app_amt, fund_type, payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, use_name, exp_func, is_deleted,mof_div_code from ")
                .append(TableName.SALARY_PAYMENT_HIS);

        sql.append(" WHERE is_deleted = ? and salary_month=? and ")
                .append(SysUtils.inSql(" agency_id ",agencyIds));
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE,month
        };
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryPaymentRequisitionBack.class), objects);
    }

    @Override
    public void batchInsertPaymentDatas(List<SalaryPaymentRequisitionBack> spList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.ALARY_PAYMENT_BACK);
        sql.append("(id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, pay_app_no, ")
                .append(" bgt_id, pay_app_amt, fund_type, payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, use_name, exp_func, is_deleted,mof_div_code )");
        sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramList = new ArrayList<>(spList.size());
        for(SalaryPaymentRequisitionBack salaryPaymentRequisitionBack : spList){
            Object[] objects = new Object[]{
                    SysUtils.generateGuid(), salaryPaymentRequisitionBack.getBizKey(), salaryPaymentRequisitionBack.getCreateTime(),
                    salaryPaymentRequisitionBack.getUpdateTime(), salaryPaymentRequisitionBack.getAgencyId(),
                    salaryPaymentRequisitionBack.getAgencyName(), salaryPaymentRequisitionBack.getPayId(), salaryPaymentRequisitionBack.getPayAppNo(),
                    salaryPaymentRequisitionBack.getBgtId(),
                    salaryPaymentRequisitionBack.getPayAppAmt(), salaryPaymentRequisitionBack.getFundType(), salaryPaymentRequisitionBack.getPayeeAcctName(),
                    salaryPaymentRequisitionBack.getPayeeAcctNo(), salaryPaymentRequisitionBack.getPayeeAcctBankName(), salaryPaymentRequisitionBack.getPayType(),
                    salaryPaymentRequisitionBack.getProCat(), salaryPaymentRequisitionBack.getGovBgtEco(), salaryPaymentRequisitionBack.getDepBgtEco(),
                    salaryPaymentRequisitionBack.getPro(), salaryPaymentRequisitionBack.getUseName(), salaryPaymentRequisitionBack.getExpFunc(),
                    salaryPaymentRequisitionBack.getIsDeleted(),salaryPaymentRequisitionBack.getMofDivCode()
            };
            paramList.add(objects);
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 23; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deletePaymentHistory(String month, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete  from ").append(TableName.SALARY_PAYMENT_HIS);
        sql.append(" where  ").append(SysUtils.inSql(" agency_id ",agencyIds));
        sql.append(" and salary_month=? ");
        this.getjdbcTemplate().update(sql.toString(),new Object[]{month});
    }



    @Override
    public List<SalaryBankPayBack> getBankBackDatas(List<String> agencyIds) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ")
                .append("id, biz_key, create_time, update_time, iden_no, person_name, old_sala_card_no, new_sala_card_no,  ")
                .append("bank_back_code, bank_back_remark, operate_code, is_update, verified_in_agency_code, verified_code, is_recreate, is_long_use, is_send, is_update_person, ")
                .append(" mof_div_code, month_id, new_person_name, new_iden_no, new_salary_bank, person_id, salary_month,agency_id from ")
                .append(TableName.SALARY_BANK_HIS);

        sql.append(" WHERE  ")
                .append(SysUtils.inSql(" agency_id ",agencyIds));

        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryBankPayBack.class));
    }
    @Override
    public void deleteBankBackDatas(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete  from ").append(TableName.SALARY_BANK_HIS);
        sql.append(" where  ").append(SysUtils.inSql(" agency_id ",agencyIds));

        this.getjdbcTemplate().update(sql.toString());
    }
    @Override
    public void batchInsertBankBackHistoryDatas(List<SalaryBankPayBack> bankBacks) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_BANK_BACK);
        sql.append("(id, biz_key, create_time, update_time, iden_no, person_name, old_sala_card_no, new_sala_card_no,  ")
                .append("bank_back_code, bank_back_remark, operate_code, is_update, verified_in_agency_code, verified_code,is_recreate, is_long_use, is_send, is_update_person, ")
                .append(" mof_div_code, month_id, new_person_name, new_iden_no, new_salary_bank, person_id, agency_id )");
        sql.append(" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        List<Object[]> paramList = new ArrayList<>(bankBacks.size());
        for(SalaryBankPayBack bankBack : bankBacks){
            Object[] objects = new Object[]{
                    SysUtils.generateGuid(), bankBack.getBizKey(),bankBack.getCreateTime(),
                    bankBack.getUpdateTime(), bankBack.getIdenNo(),
                    bankBack.getPersonName(), bankBack.getOldSalaCardNo(), bankBack.getNewSalaCardNo(),
                    bankBack.getBankBackCode(),
                    bankBack.getBankBackRemark(), bankBack.getOperateCode(), bankBack.getIsUpdate(),
                    bankBack.getVerifiedInAgencyCode(), bankBack.getVerifiedCode(), bankBack.getIsRecreate(),bankBack.getIsLongUse(),bankBack.getIsSend(),bankBack.getIsUpdatePerson(),
                    bankBack.getMofDivCode(), bankBack.getMonthId(), bankBack.getNewPersonName(),
                    bankBack.getNewIdenNo(), bankBack.getNewSalaryBank(), bankBack.getPersonId(),
                    bankBack.getAgencyId()
            };
            paramList.add(objects);
        }

        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 25; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }


}
